<!-- TOC -->

- [Введение](#%D0%B2%D0%B2%D0%B5%D0%B4%D0%B5%D0%BD%D0%B8%D0%B5)
- [Представления](#%D0%BF%D1%80%D0%B5%D0%B4%D1%81%D1%82%D0%B0%D0%B2%D0%BB%D0%B5%D0%BD%D0%B8%D1%8F)
- [Внешние ключи](#%D0%B2%D0%BD%D0%B5%D1%88%D0%BD%D0%B8%D0%B5-%D0%BA%D0%BB%D1%8E%D1%87%D0%B8)
- [Транзакции](#%D1%82%D1%80%D0%B0%D0%BD%D0%B7%D0%B0%D0%BA%D1%86%D0%B8%D0%B8)
- [Оконные функции](#%D0%BE%D0%BA%D0%BE%D0%BD%D0%BD%D1%8B%D0%B5-%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D0%B8)
- [Наследование](#%D0%BD%D0%B0%D1%81%D0%BB%D0%B5%D0%B4%D0%BE%D0%B2%D0%B0%D0%BD%D0%B8%D0%B5)
- [Заключение](#%D0%B7%D0%B0%D0%BA%D0%BB%D1%8E%D1%87%D0%B5%D0%BD%D0%B8%D0%B5)

<!-- /TOC -->
<https://postgrespro.ru/docs/postgrespro/12/tutorial-advanced>

# [Введение](https://postgrespro.ru/docs/postgrespro/12/tutorial-advanced-intro)

В предыдущей главе мы изучили азы использования SQL для хранения и обработки данных в Postgres Pro. Теперь мы обсудим более сложные возможности SQL, помогающие управлять данными и предотвратить их потерю или порчу. В конце главы мы рассмотрим некоторые расширения Postgres Pro.

В этой главе мы будем время от времени ссылаться на примеры, приведённые в Главе 2 и изменять или развивать их, поэтому будет полезно сначала прочитать предыдущую главу. Некоторые примеры этой главы также можно найти в файле advanced.sql в каталоге tutorial. Кроме того, этот файл содержит пример данных для загрузки (здесь она повторно не рассматривается). Если вы не знаете, как использовать этот файл, обратитесь к Разделу 2.1.

# [Представления](https://postgrespro.ru/docs/postgrespro/12/tutorial-views)

Вспомните запросы, с которыми мы имели дело в Разделе 2.6. Предположим, что вас интересует составной список из погодных записей и координат городов, но вы не хотите каждый раз вводить весь этот запрос. Вы можете создать представление по данному запросу, фактически присвоить имя запросу, а затем обращаться к нему как к обычной таблице:

    CREATE VIEW myview AS
        SELECT city, temp_lo, temp_hi, prcp, date, location
            FROM weather, cities
            WHERE city = name;

    SELECT * FROM myview;
Активное использование представлений — это ключевой аспект хорошего проектирования баз данных SQL. Представления позволяют вам скрыть внутреннее устройство ваших таблиц, которые могут меняться по мере развития приложения, за надёжными интерфейсами.

Представления можно использовать практически везде, где можно использовать обычные таблицы. И довольно часто представления создаются на базе других представлений.

# [Внешние ключи](https://postgrespro.ru/docs/postgrespro/12/tutorial-fk)

Вспомните таблицы weather и cities из Главы 2. Давайте рассмотрим следующую задачу: вы хотите добиться, чтобы никто не мог вставить в таблицу weather строки, для которых не находится соответствующая строка в таблице cities. Это называется обеспечением ссылочной целостности данных. В простых СУБД это пришлось бы реализовать (если это вообще возможно) так: сначала явно проверить, есть ли соответствующие записи в таблице cities, а затем отклонить или вставить новые записи в таблицу weather. Этот подход очень проблематичен и неудобен, поэтому всё это Postgres Pro может сделать за вас.

Новое объявление таблицы будет выглядеть так:

    CREATE TABLE cities (
            city     varchar(80) primary key,
            location point
    );

    CREATE TABLE weather (
            city      varchar(80) references cities(city),
            temp_lo   int,
            temp_hi   int,
            prcp      real,
            date      date
    );
Теперь попробуйте вставить недопустимую запись:

    INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
    
^

    ОШИБКА:  INSERT или UPDATE в таблице "weather" нарушает ограничение внешнего
    ключа "weather_city_fkey"
    ПОДРОБНОСТИ:  Ключ (city)=(Berkeley) отсутствует в таблице "cities".
Поведение внешних ключей можно подстроить согласно требованиям вашего приложения. Мы не будем усложнять этот простой пример в данном введении, но вы можете обратиться за дополнительной информацией к Главе 5. Правильно применяя внешние ключи, вы определённо создадите более качественные приложения, поэтому мы настоятельно рекомендуем изучить их.



# [Транзакции](https://postgrespro.ru/docs/postgrespro/12/tutorial-transactions)

Транзакции — это фундаментальное понятие во всех СУБД. Суть транзакции в том, что она объединяет последовательность действий в одну операцию "всё или ничего". Промежуточные состояния внутри последовательности не видны другим транзакциям, и если что-то помешает успешно завершить транзакцию, ни один из результатов этих действий не сохранится в базе данных.

Например, рассмотрим базу данных банка, в которой содержится информация о счетах клиентов, а также общие суммы по отделениям банка. Предположим, что мы хотим перевести 100 долларов со счёта Алисы на счёт Боба. Простоты ради, соответствующие SQL-команды можно записать так:

    UPDATE accounts SET balance = balance - 100.00
        WHERE name = 'Alice';
    UPDATE branches SET balance = balance - 100.00
        WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
    UPDATE accounts SET balance = balance + 100.00
        WHERE name = 'Bob';
    UPDATE branches SET balance = balance + 100.00
        WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
Точное содержание команд здесь не важно, важно лишь то, что для выполнения этой довольно простой операции потребовалось несколько отдельных действий. При этом с точки зрения банка необходимо, чтобы все эти действия выполнились вместе, либо не выполнились совсем. Если Боб получит 100 долларов, но они не будут списаны со счёта Алисы, объяснить это сбоем системы определённо не удастся. И наоборот, Алиса вряд ли будет довольна, если она переведёт деньги, а до Боба они не дойдут. Нам нужна гарантия, что если что-то помешает выполнить операцию до конца, ни одно из действий не оставит следа в базе данных. И мы получаем эту гарантию, объединяя действия в одну транзакцию. Говорят, что транзакция атомарна: с точки зрения других транзакций она либо выполняется и фиксируется полностью, либо не фиксируется совсем.

Нам также нужна гарантия, что после завершения и подтверждения транзакции системой баз данных, её результаты в самом деле сохраняются и не будут потеряны, даже если вскоре произойдёт авария. Например, если мы списали сумму и выдали её Бобу, мы должны исключить возможность того, что сумма на его счёте восстановится, как только он выйдет за двери банка. Транзакционная база данных гарантирует, что все изменения записываются в постоянное хранилище (например, на диск) до того, как транзакция будет считаться завершённой.

Другая важная характеристика транзакционных баз данных тесно связана с атомарностью изменений: когда одновременно выполняется множество транзакций, каждая из них не видит незавершённые изменения, произведённые другими. Например, если одна транзакция подсчитывает баланс по отделениям, будет неправильно, если она посчитает расход в отделении Алисы, но не учтёт приход в отделении Боба, или наоборот. Поэтому свойство транзакций "всё или ничего" должно определять не только, как изменения сохраняются в базе данных, но и как они видны в процессе работы. Изменения, производимые открытой транзакцией, невидимы для других транзакций, пока она не будет завершена, а затем они становятся видны все сразу.

В Postgres Pro транзакция определяется набором SQL-команд, окружённым командами BEGIN и COMMIT. Таким образом, наша банковская транзакция должна была бы выглядеть так:

    BEGIN;
    UPDATE accounts SET balance = balance - 100.00
        WHERE name = 'Alice';
    -- ...
    COMMIT;
Если в процессе выполнения транзакции мы решим, что не хотим фиксировать её изменения (например, потому что оказалось, что баланс Алисы стал отрицательным), мы можем выполнить команду ROLLBACK вместо COMMIT, и все наши изменения будут отменены.

Postgres Pro на самом деле отрабатывает каждый SQL-оператор как транзакцию. Если вы не вставите команду BEGIN, то каждый отдельный оператор будет неявно окружён командами BEGIN и COMMIT (в случае успешного завершения). Группу операторов, окружённых командами BEGIN и COMMIT иногда называют блоком транзакции.

> Примечание
Некоторые клиентские библиотеки добавляют команды BEGIN и COMMIT автоматически и неявно создают за вас блоки транзакций. Подробнее об этом вы можете узнать в документации интересующего вас интерфейса.

Операторами в транзакции можно также управлять на более детальном уровне, используя точки сохранения. Точки сохранения позволяют выборочно отменять некоторые части транзакции и фиксировать все остальные. Определив точку сохранения с помощью SAVEPOINT, при необходимости вы можете вернуться к ней с помощью команды ROLLBACK TO. Все изменения в базе данных, произошедшие после точки сохранения и до момента отката, отменяются, но изменения, произведённые ранее, сохраняются.

Когда вы возвращаетесь к точке сохранения, она продолжает существовать, так что вы можете откатываться к ней несколько раз. С другой стороны, если вы уверены, что вам не придётся откатываться к определённой точке сохранения, её можно удалить, чтобы система высвободила ресурсы. Помните, что при удалении или откате к точке сохранения все точки сохранения, определённые после неё, автоматически уничтожаются.

Всё это происходит в блоке транзакции, так что в других сеансах работы с базой данных этого не видно. Совершённые действия становятся видны для других сеансов все сразу, только когда вы фиксируете транзакцию, а отменённые действия не видны вообще никогда.

Вернувшись к банковской базе данных, предположим, что мы списываем 100 долларов со счёта Алисы, добавляем их на счёт Боба, и вдруг оказывается, что деньги нужно было перевести Уолли. В данном случае мы можем применить точки сохранения:

    BEGIN;
    UPDATE accounts SET balance = balance - 100.00
        WHERE name = 'Alice';
    SAVEPOINT my_savepoint;
    UPDATE accounts SET balance = balance + 100.00
        WHERE name = 'Bob';
    -- ошибочное действие... забыть его и использовать счёт Уолли
    ROLLBACK TO my_savepoint;
    UPDATE accounts SET balance = balance + 100.00
        WHERE name = 'Wally';
    COMMIT;
Этот пример, конечно, несколько надуман, но он показывает, как можно управлять выполнением команд в блоке транзакций, используя точки сохранения. Более того, ROLLBACK TO — это единственный способ вернуть контроль над блоком транзакций, оказавшимся в прерванном состоянии из-за ошибки системы, не считая возможности полностью отменить её и начать снова.



# [Оконные функции](https://postgrespro.ru/docs/postgrespro/12/tutorial-window)

Оконная функция выполняет вычисления для набора строк, некоторым образом связанных с текущей строкой. Её действие можно сравнить с вычислением, производимым агрегатной функцией. Однако с оконными функциями строки не группируются в одну выходную строку, что имеет место с обычными, не оконными, агрегатными функциями. Вместо этого, эти строки остаются отдельными сущностями. Внутри же, оконная функция, как и агрегатная, может обращаться не только к текущей строке результата запроса.

Вот пример, показывающий, как сравнить зарплату каждого сотрудника со средней зарплатой его отдела:

    SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname)
    FROM empsalary;
    depname  | empno | salary |          avg          
    -----------+-------+--------+-----------------------
    develop   |    11 |   5200 | 5020.0000000000000000
    develop   |     7 |   4200 | 5020.0000000000000000
    develop   |     9 |   4500 | 5020.0000000000000000
    develop   |     8 |   6000 | 5020.0000000000000000
    develop   |    10 |   5200 | 5020.0000000000000000
    personnel |     5 |   3500 | 3700.0000000000000000
    personnel |     2 |   3900 | 3700.0000000000000000
    sales     |     3 |   4800 | 4866.6666666666666667
    sales     |     1 |   5000 | 4866.6666666666666667
    sales     |     4 |   4800 | 4866.6666666666666667
    (10 rows)
Первые три столбца извлекаются непосредственно из таблицы empsalary, при этом для каждой строки таблицы есть строка результата. В четвёртом столбце оказалось среднее значение, вычисленное по всем строкам, имеющим то же значение depname, что и текущая строка. (Фактически среднее вычисляет та же обычная, не оконная функция avg, но предложение OVER превращает её в оконную, так что её действие ограничивается рамками окон.)

Вызов оконной функции всегда содержит предложение OVER, следующее за названием и аргументами оконной функции. Это синтаксически отличает её от обычной, не оконной агрегатной функции. Предложение OVER определяет, как именно нужно разделить строки запроса для обработки оконной функцией. Предложение PARTITION BY, дополняющее OVER, разделяет строки по группам, или разделам, объединяя одинаковые значения выражений PARTITION BY. Оконная функция вычисляется по строкам, попадающим в один раздел с текущей строкой.

Вы можете также определять порядок, в котором строки будут обрабатываться оконными функциями, используя ORDER BY в OVER. (Порядок ORDER BY для окна может даже не совпадать с порядком, в котором выводятся строки.) Например:

    SELECT depname, empno, salary,
        rank() OVER (PARTITION BY depname ORDER BY salary DESC)
    FROM empsalary;
    depname  | empno | salary | rank 
    -----------+-------+--------+------
    develop   |     8 |   6000 |    1
    develop   |    10 |   5200 |    2
    develop   |    11 |   5200 |    2
    develop   |     9 |   4500 |    4
    develop   |     7 |   4200 |    5
    personnel |     2 |   3900 |    1
    personnel |     5 |   3500 |    2
    sales     |     1 |   5000 |    1
    sales     |     4 |   4800 |    2
    sales     |     3 |   4800 |    2
    (10 rows)
Как показано здесь, функция rank выдаёт порядковый номер для каждого уникального значения в разделе текущей строки, по которому выполняет сортировку предложение ORDER BY. У функции rank нет параметров, так как её поведение полностью определяется предложением OVER.

Строки, обрабатываемые оконной функцией, представляют собой «виртуальные таблицы», созданные из предложения FROM и затем прошедшие через фильтрацию и группировку WHERE и GROUP BY и, возможно, условие HAVING. Например, строка, отфильтрованная из-за нарушения условия WHERE, не будет видна для оконных функций. Запрос может содержать несколько оконных функций, разделяющих данные по-разному с применением разных предложений OVER, но все они будут обрабатывать один и тот же набор строк этой виртуальной таблицы.

Мы уже видели, что ORDER BY можно опустить, если порядок строк не важен. Также возможно опустить PARTITION BY, в этом случае образуется один раздел, содержащий все строки.

Есть ещё одно важное понятие, связанное с оконными функциями: для каждой строки существует набор строк в её разделе, называемый рамкой окна. Некоторые оконные функции обрабатывают только строки рамки окна, а не всего раздела. По умолчанию с указанием ORDER BY рамка состоит из всех строк от начала раздела до текущей строки и строк, равных текущей по значению выражения ORDER BY. Без ORDER BY рамка по умолчанию состоит из всех строк раздела. [4] Посмотрите на пример использования sum:

    SELECT salary, sum(salary) OVER () FROM empsalary;
    salary |  sum  
    --------+-------
    5200 | 47100
    5000 | 47100
    3500 | 47100
    4800 | 47100
    3900 | 47100
    4200 | 47100
    4500 | 47100
    4800 | 47100
    6000 | 47100
    5200 | 47100
    (10 rows)
Так как в этом примере нет указания ORDER BY в предложении OVER, рамка окна содержит все строки раздела, а он, в свою очередь, без предложения PARTITION BY включает все строки таблицы; другими словами, сумма вычисляется по всей таблице и мы получаем один результат для каждой строки результата. Но если мы добавим ORDER BY, мы получим совсем другие результаты:

    SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
    salary |  sum  
    --------+-------
    3500 |  3500
    3900 |  7400
    4200 | 11600
    4500 | 16100
    4800 | 25700
    4800 | 25700
    5000 | 30700
    5200 | 41100
    5200 | 41100
    6000 | 47100
    (10 rows)
Здесь в сумме накапливаются зарплаты от первой (самой низкой) до текущей, включая повторяющиеся текущие значения (обратите внимание на результат в строках с одинаковой зарплатой).

Оконные функции разрешается использовать в запросе только в списке SELECT и предложении ORDER BY. Во всех остальных предложениях, включая GROUP BY, HAVING и WHERE, они запрещены. Это объясняется тем, что логически они выполняются после этих предложений, а также после не оконных агрегатных функций, и значит агрегатную функцию можно вызывать в аргументах оконной, но не наоборот.

Если вам нужно отфильтровать или сгруппировать строки после вычисления оконных функций, вы можете использовать вложенный запрос. Например:

    SELECT depname, empno, salary, enroll_date
    FROM
    (SELECT depname, empno, salary, enroll_date,
        rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
    FROM empsalary
    ) AS ss
    WHERE pos < 3;
Данный запрос покажет только те строки внутреннего запроса, у которых rank (порядковый номер) меньше 3.

Когда в запросе вычисляются несколько оконных функций для одинаково определённых окон, конечно можно написать для каждой из них отдельное предложение OVER, но при этом оно будет дублироваться, что неизбежно будет провоцировать ошибки. Поэтому лучше определение окна выделить в предложение WINDOW, а затем ссылаться на него в OVER. Например:

    SELECT sum(salary) OVER w, avg(salary) OVER w
    FROM empsalary
    WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
Подробнее об оконных функциях можно узнать в Подразделе 4.2.8, Разделе 9.21, Подразделе 7.2.5 и в справке SELECT.

# [Наследование](https://postgrespro.ru/docs/postgrespro/12/tutorial-inheritance)

Наследование — это концепция, взятая из объектно-ориентированных баз данных. Она открывает множество интересных возможностей при проектировании баз данных.

Давайте создадим две таблицы: cities (города) и capitals (столицы штатов). Естественно, столицы штатов также являются городами, поэтому нам нужно явным образом добавлять их в результат, когда мы хотим просмотреть все города. Если вы проявите смекалку, вы можете предложить, например, такое решение:

    CREATE TABLE capitals (
    name       text,
    population real,
    altitude   int,    -- (высота в футах)
    state      char(2)
    );

    CREATE TABLE non_capitals (
    name       text,
    population real,
    altitude   int     -- (высота в футах)
    );

    CREATE VIEW cities AS
    SELECT name, population, altitude FROM capitals
        UNION
    SELECT name, population, altitude FROM non_capitals;
Оно может устраивать, пока мы извлекаем данные, но если нам потребуется изменить несколько строк, это будет выглядеть некрасиво.

Поэтому есть лучшее решение:

    CREATE TABLE cities (
    name       text,
    population real,
    altitude   int     -- (высота в футах)
    );

    CREATE TABLE capitals (
    state      char(2)
    ) INHERITS (cities);
В данном случае строка таблицы capitals наследует все столбцы (name, population и altitude) от родительской таблицы cities. Столбец name имеет тип text, собственный тип Postgres Pro для текстовых строк переменной длины. А в таблицу столиц добавлен дополнительный столбец state, в котором будет указан штат. В Postgres Pro таблица может наследоваться от нуля или нескольких других таблиц.

Например, следующий запрос выведет названия всех городов, включая столицы, находящихся выше 500 футов над уровнем моря:

    SELECT name, altitude
    FROM cities
    WHERE altitude > 500;
    Результат его выполнения:

    name    | altitude
    -----------+----------
    Las Vegas |     2174
    Mariposa  |     1953
    Madison   |      845
    (3 rows)
А следующий запрос находит все города, которые не являются столицами штатов, но также находятся выше 500 футов:

    SELECT name, altitude
        FROM ONLY cities
        WHERE altitude > 500;
    name    | altitude
    -----------+----------
    Las Vegas |     2174
    Mariposa  |     1953
    (2 rows)
Здесь слово ONLY перед названием таблицы cities указывает, что запрос следует выполнять только для строк таблицы cities, не включая таблицы, унаследованные от cities. Многие операторы, которые мы уже обсудили — SELECT, UPDATE и DELETE — поддерживают указание ONLY.

> Примечание
Хотя наследование часто бывает полезно, оно не интегрировано с ограничениями уникальности и внешними ключами, что ограничивает его применимость. Подробнее это описывается в Разделе 5.10.



# [Заключение](https://postgrespro.ru/docs/postgrespro/12/tutorial-conclusion)

Postgres Pro имеет множество возможностей, не затронутых в этом кратком введении, рассчитанном на начинающих пользователей SQL. Эти возможности будут рассмотрены в деталях в продолжении книги.

Если вам необходима дополнительная вводная информация, посетите сайт PostgreSQL, там вы найдёте ссылки на другие ресурсы.